using System;
using System.Collections.Generic;
using ADEVSO.Data.Sql;

namespace ADEVSO.Data.SqlClient
{
    public class SqlInsertMaker
    {
        #region Variables

        private String _Database;
        private String _TableName;
        private List<Column> _Columns;
        private List<Column> _ColumnsWithInfo;
        private SqlClient.SqlServer _SqlConnection;

        #endregion

        #region Constructors

        public SqlInsertMaker(String Database, String TableName, List<Column> Columns, List<Column> ColumnsWithInfo, SqlClient.SqlServer DBConnection)
        {
            _Database = Database;
            _TableName = TableName;
            _Columns = Columns;
            _ColumnsWithInfo = ColumnsWithInfo;
            _SqlConnection = DBConnection;
        }

        #endregion

        #region Methods

        /// <summary>
        /// Create SQL Insert
        /// </summary>
        /// <returns></returns>
        private String SQL()
        {
            return MakeDeclareStatement() + MakeColumnsStatement() + MakeValuesStatement();
        }

        /// <summary>
        /// Make Insert Declare Statement
        /// </summary>
        /// <returns></returns>
        private String MakeDeclareStatement()
        {
            return String.Format("INSERT INTO {0}", _TableName);
        }

        /// <summary>
        /// Make Insert Columns Statement
        /// </summary>
        /// <returns></returns>
        private String MakeColumnsStatement()
        {
            String sql = "(";
            for (int i = 0; i < _ColumnsWithInfo.Count; i++)
            {
                Column column = _ColumnsWithInfo[i];
                if (i != _ColumnsWithInfo.Count - 1)
                    sql += column.ColumnName + ", ";
                else sql += column.ColumnName;
            }
            sql += ") ";
            return sql;
        }

        /// <summary>
        /// Make Insert Values Statement
        /// </summary>
        /// <returns></returns>
        private String MakeValuesStatement()
        {
            String sql = "VALUES(";
            for (int i = 0; i < _ColumnsWithInfo.Count; i++)
            {
                Column column = _ColumnsWithInfo[i];
                if (i != _ColumnsWithInfo.Count - 1)
                    sql += "@" + column.ColumnName + ", ";
                else sql += "@" + column.ColumnName;
            }
            sql += ");";
            return sql;
        }

        /// <summary>
        /// Validate All Not Null Columns have Info Mapped
        /// </summary>
        /// <returns></returns>
        private bool ValidateNotNullColumns()
        {
            bool result = false;
            for (int i = 0; i < _Columns.Count; i++)
            {
                Column column = (Column)_Columns[i];
                if (!column.AllowNulls)
                {
                    result = false;
                    for (int j = 0; j < _ColumnsWithInfo.Count; j++)
                    {
                        Column columnWithInfo = (Column)_ColumnsWithInfo[j];
                        if (column.ColumnName.Equals(columnWithInfo.ColumnName) && ! columnWithInfo.Value.ToString().Trim().Equals(""))
                        {
                            result = true;
                            break;
                        }
                    }
                    if (!result)
                        break;
                }
            }
            return result;
        }

        /// <summary>
        /// Get SQL Parameter
        /// </summary>
        /// <param name="DataType"></param>
        /// <param name="ColumnName"></param>
        /// <param name="Value"></param>
        /// <returns></returns>
        private System.Data.SqlClient.SqlParameter GetParameter(String DataType, String ColumnName, Object Value)
        {
            System.Data.SqlClient.SqlParameter result = null;
            switch (DataType)
            {
                case "bigint":      { result = new System.Data.SqlClient.SqlParameter("@" + ColumnName, System.Data.SqlDbType.BigInt); result.Value = Value; break; }
                case "char":        { result = new System.Data.SqlClient.SqlParameter("@" + ColumnName, System.Data.SqlDbType.Char); result.Value = Value; break; }
                case "datetime":    { result = new System.Data.SqlClient.SqlParameter("@" + ColumnName, System.Data.SqlDbType.DateTime); result.Value = Value; break; }
                case "decimal":     { result = new System.Data.SqlClient.SqlParameter("@" + ColumnName, System.Data.SqlDbType.Decimal); result.Value = Value; break; }
                case "float":       { result = new System.Data.SqlClient.SqlParameter("@" + ColumnName, System.Data.SqlDbType.Float); result.Value = Value; break; }
                case "int":         { result = new System.Data.SqlClient.SqlParameter("@" + ColumnName, System.Data.SqlDbType.Int); result.Value = Value; break; }
                case "text":        { result = new System.Data.SqlClient.SqlParameter("@" + ColumnName, System.Data.SqlDbType.Text); result.Value = Value; break; }
                case "varchar":     { result = new System.Data.SqlClient.SqlParameter("@" + ColumnName, System.Data.SqlDbType.VarChar); result.Value = Value; break; }
            }
            return result;
        }

        /// <summary>
        /// Run SQL Insert
        /// </summary>
        public void Run()
        {
            if (ValidateNotNullColumns())
            {
                System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(_SqlConnection.InnerConnection.ConnectionString);
                connection.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(SQL(), connection);
                for (int i = 0; i < _ColumnsWithInfo.Count; i++)
                {
                    Column columnWithInfo = (Column)_ColumnsWithInfo[i];
                    cmd.Parameters.Add(GetParameter(columnWithInfo.DataType, columnWithInfo.ColumnName, Common.GetDataType(columnWithInfo.DataType, columnWithInfo.Value)));
                }
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            else throw new Exception("SQL Insert Must Have Info To All Not Null Table Columns");
        }

        #endregion
    }
}